﻿using System;
using System.Collections.Generic;
using System.Text;

namespace KSS.Generator
{
    public class Template
    {
        public const string Tab = "\t";
        public const string Tab2 = "\t\t";

        public const string LOCAL_PROPERTY_CHARACTER = "$";
        public const string GLOBAL_PROPERTY_CHARACTER = "$$";
        public const string CUSTOM_PROPERTY_CHARACTER = "$$$";

        public const string TEMPLATE_FILE_EXTENSION = ".tpl";

        public class DotNetDataType
        {
            public const string Boolean = "System.Boolean";
            public const string Int16 = "System.Int16";
            public const string Int32 = "System.Int32";
            public const string Int64 = "System.Int64";
            public const string Byte = "System.Byte";
            public const string SByte = "System.SByte";
            public const string Single = "System.Single";
            public const string UInt16 = "System.UInt16";
            public const string UInt32 = "System.UInt32";
            public const string UInt64 = "System.UInt64";
            public const string Decimal = "System.Decimal";
            public const string Double = "System.Double";
            public const string Guid = "System.Guid";
            public const string Char = "System.Char";
            public const string String = "System.String";
            public const string DateTime = "System.DateTime";
        }

        public class CSharpDataType
        {
            public const string Boolean = "bool";
            public const string Int16 = "short";
            public const string Int32 = "int";
            public const string Int64 = "long";
            public const string Byte = "byte";
            public const string SByte = "sbyte";
            public const string Single = "float";
            public const string UInt16 = "ushort";
            public const string UInt32 = "uint";
            public const string UInt64 = "ulong";
            public const string Decimal = "decimal";
            public const string Double = "double";
            public const string Guid = "Guid";
            public const string Char = "char";
            public const string String = "string";
            public const string DateTime = "DateTime";
        }

        #region [ Global ]
        public class Global
        {
            public const string Tab = "\t";
            public const string Tab2 = "\t\t";

            public const string Local_Property_Character = "$";
            public const string Global_Property_Character = "$$";
            public const string Custom_Property_Character = "$$$";

            public const string Template_File_Extension = ".tpl";

            /// <summary>
            /// ref/out int Var
            /// </summary>
            public const string Declaration = "{0} {1} {2}";

            public const string ParameterDirectionInOut = "InOut";
        }
        #endregion

        #region [ Project ]

        public class Project
        {
            public const string DataProjectName = "Data";
            public const string EntityProjectName = "Entity";
            public const string ManagerProjectName = "Manager";
            public const string LinqMapperProjectName = "Linq_Mapper";
            public const string SqlSchemaProjectName = "Sql_Schema";
            public const string SqlMapperProjectName = "Sql_Mapper";
            public const string LinqProjectName = "Linq";
            public const string SqlProjectName = "Sql";
        }
        #endregion

        #region [ Entity project ]
        public class EntityProject
        {
            /// <summary>
            /// Entity template file
            /// </summary>
            public const string CustomEntityFileName = "[EntityName].cs.tpl";

            /// <summary>
            /// Entity template file
            /// </summary>
            public const string GeneratedEntityFileName = "[EntityName].generated.cs.tpl";

            /// <summary>
            /// Store result entity template file
            /// </summary>
            public const string CustomStoreResultFileName = "[StoreResultName].cs.tpl";

            /// <summary>
            /// Store result entity template file
            /// </summary>
            public const string GeneratedStoreResultFileName = "[StoreResultName].generated.cs.tpl";

            /// <summary>
            /// private DataType(?) _columnName;
            /// {0}: _columnName
            /// {1}: Data type
            /// {2}: Accept null
            /// </summary>
            public const string Variable = @"
        private {1} {0};";

            /// <summary>
            /// public DataType(?) ColumnName
            /// {0}: ColumnName
            /// {1}: Data type
            /// {2}: Accept null
            /// {3}: _columnName
            /// </summary>
            public const string Property = @"
        public {1} {0}
        (
            get ( return {2}; )
            set ( {2} = value; )
        )";

            public const string CustomTag_EntityName = "EntityName";

            public const string CustomTag_PropertyDeclaration = "PropertyDeclaration";

        }
        #endregion

        #region [ Data project ]

        public class DataProject
        {
            /// <summary>
            /// Data project provider file
            /// </summary>
            public const string ProviderFileName = "Provider.cs.tpl";

            /// <summary>
            /// Place holder text
            /// </summary>
            public const string GeneratedCode = "GeneratedCode";

            /// <summary>
            /// 
            /// </summary>
            public const string GeneratedCodeRegion = "#region [ Generated methods ]";

            /// <summary>
            /// 
            /// </summary>
            public const string CustomCodeRegion = "#region [ Custom methods ]";

            /// <summary>
            /// Region start
            /// {0}: entity name
            /// </summary>
            public const string EntityRegionStart = "#region [ {0} ]";

            /// <summary>
            /// Region end 
            /// </summary>
            public const string EndRegion = "#endregion";

            /// <summary>
            /// public abstract int GetEntityNameCount();
            /// {0}: entity name
            /// </summary>
            public const string GetCount = @"public abstract int Get{0}Count();";

            /// <summary>
            /// public abstract EntityName GetEntityNameByPrimarykey(DataType PrimaryKeyVar);
            /// To be used when result has only one primary key
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetByPrimaryKey = "public abstract {0} Get{0}By{1}({2} {3});";

            /// <summary>
            /// public abstract EntityName GetEntityNameByPrimarykey(DataType PrimaryKeyVar);
            /// To be used when result has more than one primary key
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetListByPrimaryKey = "public abstract List<{0}> Get{0}By{1}({2} {3});";

            /// <summary>
            /// public abstract EntityName GetEntityNameByPrimarykey(DataType PrimaryKeyVar);
            /// To be used when result has more than one primary key
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetListByPrimaryKeyWithPaging = "public abstract List<{0}> Get{0}By{1}WithPaging({2} {3}, int pageIndex, int pageSize);";

            /// <summary>
            /// public abstract List(EntityName) GetEntityNameByForeignKey(DataType ForeignKey);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetByForeignKey = "public abstract List<{0}> Get{0}By{1}({2} {3});";

            /// <summary>
            /// public abstract List<EntityName> GetEntityNameByForeignKeyEntity(ForeignKeyEntity ForeignKey);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: variable name
            /// </summary>
            public const string GetByForeignKeyEntity = "public abstract List<{0}> Get{0}By{1}({1} {2});";

            /// <summary>
            /// public abstract List<EntityName> GetByForeignKeyIdWithPaging(DataType ForeignKey);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetByForeignKeyWithPaging = "public abstract List<{0}> Get{0}By{1}WithPaging({2} {3}, int pageIndex, int pageSize);";

            /// <summary>
            /// public abstract List<EntityName> GetEntityNameByForeignKeyWithPaging(DataType ForeignKey, int pageIndex, int pageSize);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetByForeignKeyEntityWithPaging = "public abstract List<{0}> Get{0}By{1}WithPaging({1} {2}, int pageIndex, int pageSize);";

            /// <summary>
            /// public abstract List<EntityName> GetAllEntityName();
            /// {0}: entity name
            /// </summary>
            public const string GetAll = "public abstract List<{0}> GetAll{0}();";

            /// <summary>
            /// public abstract List<EntityName> GetAllEntityNameWithPaging(int pageIndex, int pageSize);
            /// {0}: entity name
            /// </summary>
            public const string GetAllWithPaging = "public abstract List<{0}> GetAll{0}WithPaging(int pageIndex, int pageSize);";

            /// <summary>
            /// public abstract AddEntityName(EntityName item)
            /// {0}: entity name
            /// {1}: variable name
            /// </summary>
            public const string Add = "public abstract int Add{0}({0} {1});";

            /// <summary>
            /// public abstract UpdateEntityName(EntityName item);
            /// {0}: entity name
            /// {1}: variable name
            /// </summary>
            public const string Update = "public abstract int Update{0}({0} {1});";

            /// <summary>
            /// public abstract DeleteEntityNameByPrimaryKey(DataType primaryKey);
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string DeleteByPrimaryKey = "public abstract int Delete{0}By{1}({2} {3});";

            /// <summary>
            /// public abstract DeleteEntityName(EntityName entity);
            /// {0}: entity name
            /// {1}: variable name
            /// </summary>
            public const string DeleteByPrimaryEntity = "public abstract int Delete{0}({0} {1});";

            /// <summary>
            /// public abstract DeleteEntityNameByForeignKey(DataType key);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string DeleteByForeignKey = "public abstract int Delete{0}By{1}({2} {3});";

            /// <summary>
            /// public abstract DeleteEntityNameByForeignKey(DataType key);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string DeleteByForeignEntity = "public abstract int Delete{0}By{1}({1} {2});";

            public const string StoredProcedure = "public abstract {1} {0}({2});";
        }
        #endregion

        #region [ Manager project ]

        public class ManagerProject
        {

            /// <summary>
            /// Manager template file name
            /// </summary>
            public const string CustomManagerFileName = "[EntityName]Manager.cs.tpl";

            /// <summary>
            /// Manager template file name
            /// </summary>
            public const string GeneratedManagerFileName = "[EntityName]Manager.generated.cs.tpl";

            /// <summary>
            /// Store procedure template file name
            /// </summary>
            public const string StoredProcManagerFile = "StoredProcManager.generated.cs.tpl";

            /// <summary>
            /// Custom store procedure template file name
            /// </summary>
            public const string CustomStoredProcManagerFile = "StoredProcManager.cs.tpl";

            /// <summary>
            /// Entity name custom tag
            /// </summary>
            public const string CustomTag_EntityName = "EntityName";

            /// <summary>
            /// Entity manager methods custom tag
            /// </summary>
            public const string CustomTag_EntityManagerMethods = "EntityManagerMethods";

            /// <summary>
            /// Custom tag: StoredProcedureMethods
            /// </summary>
            public const string CustomTag_StoredProcedureMethods = "StoredProcedureMethods";

            /// <summary>
            /// One method template
            /// </summary>
            public const string MethodTemplate = @"
        {0}
        @
            return DataManager.Provider.{1};
        $";

            /// <summary>
            /// public int GetEntityNameCount();
            /// {0}: entity name
            /// </summary>
            public const string GetCount = "public int Get{0}Count()";

            /// <summary>
            /// public EntityName GetEntityNameByPrimarykey(DataType PrimaryKey);
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// </summary>
            public const string GetByPrimaryKey = "public {0} Get{0}By{1}({2} {3})";

            /// <summary>
            /// public EntityName GetEntityNameByPrimarykey(DataType PrimaryKeyVar);
            /// To be used when result has more than one primary key
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetListByPrimaryKey = "public List<{0}> Get{0}By{1}({2} {3})";

            /// <summary>
            /// public EntityName GetEntityNameByPrimarykey(DataType PrimaryKeyVar);
            /// To be used when result has more than one primary key
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetListByPrimaryKeyWithPaging = "public List<{0}> Get{0}By{1}WithPaging({2} {3}, int pageIndex, int pageSize)";

            /// <summary>
            /// public List<EntityName> GetEntityNameByForeignKey(DataType ForeignKey);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// </summary>
            public const string GetByForeignKey = "public List<{0}> Get{0}By{1}({2} {3})";

            /// <summary>
            /// public List<EntityName> GetEntityNameByForeignKeyEntity(ForeignKeyEntity ForeignKey);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: foreign key entity
            /// </summary>
            public const string GetByForeignKeyEntity = "public List<{0}> Get{0}By{1}({1} {2})";

            /// <summary>
            /// public List<EntityName> GetByForeignKeyWithPaging(DataType ForeignKey, int pageIndex, int pageSize);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// </summary>
            public const string GetByForeignKeyWithPaging = "public List<{0}> Get{0}By{1}WithPaging({2} {3}, int pageIndex, int pageSize)";

            /// <summary>
            /// public List<EntityName> GetEntityNameByForeignKeyWithPaging(DataType ForeignKey, int pageIndex, int pageSize);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string GetByForeignKeyEntityWithPaging = "public List<{0}> Get{0}By{1}WithPaging({1} {2}, int pageIndex, int pageSize)";

            /// <summary>
            /// public List<EntityName> GetAllEntityName();
            /// {0}: entity name
            /// </summary>
            public const string GetAll = "public List<{0}> GetAll{0}()";

            /// <summary>
            /// public List<EntityName> GetAllEntityNameWithPaging(int pageIndex, int pageSize);
            /// {0}: entity name
            /// </summary>
            public const string GetAllWithPaging = "public List<{0}> GetAll{0}WithPaging(int pageIndex, int pageSize)";

            /// <summary>
            /// public AddEntityName(EntityName item)
            /// {0}: entity name
            /// </summary>
            public const string Add = "public int Add{0}({0} item)";

            /// <summary>
            /// public UpdateEntityName(EntityName item);
            /// {0}: entity name
            /// </summary>
            public const string Update = "public int Update{0}({0} item)";

            /// <summary>
            /// public abstract DeleteEntityNameByPrimaryKey(DataType primaryKey);
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string DeleteByPrimaryKey = "public int Delete{0}By{1}({2} {3})";

            /// <summary>
            /// public abstract DeleteEntityName(EntityName entity);
            /// {0}: entity name
            /// {1}: variable name
            /// </summary>
            public const string DeleteByPrimaryEntity = "public int Delete{0}({0} {1})";

            /// <summary>
            /// public abstract DeleteEntityNameByForeignKey(DataType key);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string DeleteByForeignKey = "public int Delete{0}By{1}({2} {3})";

            /// <summary>
            /// public abstract DeleteEntityNameByForeignKey(DataType key);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// {3}: variable name
            /// </summary>
            public const string DeleteByForeignEntity = "public int Delete{0}By{1}({1} {2})";

            /// <summary>
            /// Store procedure call
            /// </summary>
            public const string StoredProcedure = "public {1} {0}({2})";


            /// <summary>
            /// GetEntityNameCount();
            /// {0}: entity name
            /// </summary>
            public const string CallGetCount = "Get{0}Count()";

            /// <summary>
            /// GetEntityNameByPrimarykey(DataType PrimaryKey);
            /// {0}: entity name
            /// {1}: primary key name
            /// {2}: data type
            /// </summary>
            public const string CallGetByPrimaryKey = "Get{0}By{1}({2})";

            /// <summary>
            /// CallGetEntityNameByPrimarykey(PrimaryKeyVar);
            /// To be used when result has more than one primary key
            /// {0}: entity name
            /// {1}: primary key name            
            /// {2}: variable name
            /// </summary>
            public const string CallGetListByPrimaryKey = "Get{0}By{1}({2})";

            /// <summary>
            /// CallGetEntityNameByPrimarykey(PrimaryKeyVar);
            /// To be used when result has more than one primary key
            /// {0}: entity name
            /// {1}: primary key name            
            /// {2}: variable name
            /// </summary>
            public const string CallGetListByPrimaryKeyWithPaging = "Get{0}By{1}WithPaging({2}, pageIndex, pageSize)";

            /// <summary>
            /// GetEntityNameByForeignKey(DataType ForeignKey);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// </summary>
            public const string CallGetByForeignKey = "Get{0}By{1}({2})";

            /// <summary>
            /// GetEntityNameByForeignKeyEntity(ForeignKeyEntity ForeignKey);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: foreign key entity
            /// </summary>
            public const string CallGetByForeignKeyEntity = "Get{0}By{1}({2})";

            /// <summary>
            /// GetByForeignKeyWithPaging(DataType ForeignKey, int pageIndex, int pageSize);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: data type
            /// </summary>
            public const string CallGetByForeignKeyWithPaging = "Get{0}By{1}WithPaging({2}, pageIndex, pageSize)";

            /// <summary>
            /// CallGetEntityNameByForeignKeyWithPaging(ForeignKey, pageIndex, pageSize);
            /// {0}: entity name
            /// {1}: foreign key name            
            /// {2}: variable name
            /// </summary>
            public const string CallGetByForeignKeyEntityWithPaging = "Get{0}By{1}WithPaging({2}, pageIndex, pageSize)";

            /// <summary>
            /// GetAllEntityName();
            /// {0}: entity name
            /// </summary>
            public const string CallGetAll = "GetAll{0}()";

            /// <summary>
            /// GetAllEntityNameWithPaging(int pageIndex, int pageSize);
            /// {0}: entity name
            /// </summary>
            public const string CallGetAllWithPaging = "GetAll{0}WithPaging(pageIndex, pageSize)";

            /// <summary>
            /// AddEntityName(EntityName item)
            /// {0}: entity name
            /// </summary>
            public const string CallAdd = "Add{0}(item)";

            /// <summary>
            /// UpdateEntityName(EntityName item);
            /// {0}: entity name
            /// </summary>
            public const string CallUpdate = "Update{0}(item)";

            /// <summary>
            /// CallDeleteEntityNameByPrimaryKey(primaryKey);
            /// {0}: entity name
            /// {1}: primary key name            
            /// {2}: variable name
            /// </summary>
            public const string CallDeleteByPrimaryKey = "Delete{0}By{1}({2})";

            /// <summary>
            /// CallDeleteEntityName(entity);
            /// {0}: entity name
            /// {1}: variable name
            /// </summary>
            public const string CallDeleteByPrimaryEntity = "Delete{0}({1})";

            /// <summary>
            /// CallDeleteEntityNameByForeignKey(key);
            /// {0}: entity name
            /// {1}: foreign key name            
            /// {2}: variable name
            /// </summary>
            public const string CallDeleteByForeignKey = "Delete{0}By{1}({2})";

            /// <summary>
            /// CallDeleteEntityNameByForeignKey(key);
            /// {0}: entity name
            /// {1}: foreign key name
            /// {2}: variable name
            /// </summary>
            public const string CallDeleteByForeignEntity = "Delete{0}By{1}({2})";

            /// <summary>
            /// Store procedure call
            /// </summary>
            public const string CallStoredProcedure = "{0}({1})";
        }
        #endregion

        #region [ Sql schema project ]
        public class SqlSchemaProject
        {
            /// <summary>
            /// Table schema file name
            /// </summary>
            public const string CustomTableSchemaFileName = "[TableName].cs.tpl";

            /// <summary>
            /// Table schema file name
            /// </summary>
            public const string GeneratedTableSchemaFileName = "[TableName].generated.cs.tpl";

            /// <summary>
            /// Store schema file name
            /// </summary>
            public const string CustomStoreSchemaFileName = "[StoredProcedureName].cs.tpl";

            /// <summary>
            /// Generated Store schema file name
            /// </summary>
            public const string GeneratedStoreSchemaFileName = "[StoredProcedureName].generated.cs.tpl";

            /// <summary>
            /// Custom tag: TableName
            /// </summary>
            public const string CustomTag_TableName = "TableName";

            /// <summary>
            /// Custom tag: ColumnDeclaration 
            /// </summary>
            public const string CustomTag_ColumnDeclaration = "ColumnDeclaration";

            /// <summary>
            /// Custom tag: StoredProcedureName
            /// </summary>
            public const string CustomTag_StoredProcedureName = "StoredProcedureName";

            /// <summary>
            /// Custom tag: ParameterDeclaration
            /// </summary>
            public const string CustomTag_ParameterDeclaration = "ParameterDeclaration";

            /// <summary>
            /// Declaration template
            /// </summary>
            public const string Declaration = @"
        public const string {1} = ""{0}"";";

            /// <summary>
            /// Declaration template
            /// </summary>
            public const string ParameterDeclaration = @"
        public const string {1} = ""@{0}"";";
        }
        #endregion

        #region [ Sql mapper project ]

        public class SqlMapperProject
        {
            /// <summary>
            /// Mapper file name
            /// </summary>
            public const string CustomMapperFileName = "[EntityName]Mapper.cs.tpl";

            /// <summary>
            /// Mapper file name
            /// </summary>
            public const string GeneratedMapperFileName = "[EntityName]Mapper.generated.cs.tpl";

            /// <summary>
            /// Mapper file name
            /// </summary>
            public const string CustomStoreMapperFileName = "[StoreResultName]Mapper.cs.tpl";

            /// <summary>
            /// Mapper file name
            /// </summary>
            public const string GeneratedStoreMapperFileName = "[StoreResultName]Mapper.generated.cs.tpl";

            /// <summary>
            /// Custom tag: EntityName
            /// </summary>
            public const string CustomTag_EntityName = "EntityName";

            /// <summary>
            /// Custom tag: EntityReaderMapping
            /// </summary>
            public const string CustomTag_EntityReaderMapping = "EntityReaderMapping";

            /// <summary>
            /// Custom tag: EntityRowMapping
            /// </summary>
            public const string CustomTag_EntityRowMapping = "EntityRowMapping";

            /// <summary>
            /// Custom tab: ReaderMapping
            /// {0}: column name
            /// {1}: column data type
            /// </summary>
            public const string ReaderMapping = @"
                entity.{0} = ({1})record[""{0}""];";

            /// <summary>
            /// Custom tab: RowMapping
            /// {0}: column name
            /// {1}: column data type
            /// </summary>
            public const string RowMapping = @"
                entity.{0} = ({1})row[""{0}""];";
        }
        #endregion

        #region [ Sql project ]

        public class SqlProject
        {
            /// <summary>
            /// 
            /// </summary>
            public const string CustomProviderFileName = "[EntityName]Provider.cs.tpl";

            /// <summary>
            /// 
            /// </summary>
            public const string GeneratedProviderFileName = "[EntityName]Provider.generated.cs.tpl";

            /// <summary>
            /// 
            /// </summary>
            public const string GeneratedStoredProcProviderFileName = "[]StoredProcProvider.generated.cs.tpl";

            /// <summary>
            /// 
            /// </summary>
            public const string CustomStoredProcProviderFileName = "[]StoredProcProvider.cs.tpl";

            /// <summary>
            /// Custom tag EntityMethods
            /// </summary>
            public const string CustomTag_EntityMethods = "EntityMethods";

            /// <summary>
            /// Custom tag StoredProcedureMethods
            /// </summary>
            public const string CustomTag_StoredProcedureMethods = "StoredProcedureMethods";

            public const string CommandTemplate_ExecuteReturnReader = "IDataReader result = DBAccessor.ExecuteReader(command);";

            public const string CommandTemplate_ExecuteReturnTable = "DataTable result = DBAccessor.ExecuteTable(command);";

            /// <summary>
            /// Add command
            /// {0}: Table name
            /// {1}: Column name
            /// </summary>
            public const string CommandTemplate_Add = @"
            query.Terms.Add(new UpdateTerm(
                                TableSchema.{0}.{1},
                                SqlExpression.Parameter(TableSchema.{0}.{1})));";

            /// <summary>
            /// Add parameter
            /// {0}: Table name
            /// {1}: Column name
            /// {2}: Entity
            /// </summary>
            public const string CommandTemplate_TableAddInParam = @"
            DBAccessor.AddInParameter(command, TableSchema.{0}.{1}, {2}.{1});";

            /// <summary>
            /// Add parameter
            /// {0}: Table name
            /// {1}: Column name
            /// </summary>
            public const string CommandTemplate_StoredProcAddInParam = @"
            DBAccessor.AddInParameter(command, StoredProcSchema.{0}.{1}, {2});";

            public const string CommandTemplate_ExcuteCommandSimpleReturn = @"return (int)DBAccessor.ExecuteScalar(command);";

            public const string CommandTemplate_ExcuteCommandComplexReturn = @"{1}
                {0}Mapper mapper = new {0}Mapper();
                return mapper.Map(result);";

            /// <summary>
            /// Add parameter
            /// {0}: Store procedure name
            /// {1}: Store procedure parameter
            /// </summary>
            public const string CommandTemplate_StoredProcAddOutParam = @"
            DBAccessor.AddOutParameter(command, StoredProcSchema.{0}.{1});";

            /// <summary>
            /// CommandTemplate_GetPropertyValue
            /// {0}: Stored procedure name
            /// {1}: Parameter name
            /// {2}: Parameter variable
            /// {3}: Return type
            /// </summary>
            public const string CommandTemplate_GetPropertyValue = @"
                {2} = ({3})DBAccessor.GetParameterValue(command, StoredProcSchema.{0}.{1});";

            public const string CommandTemplate_ExecuteReturnSPTable = "DataTable return_value = DBAccessor.ExecuteTable(command);";

            /// <summary>
            /// FunctionTemplate_GetCount
            /// {0}: Entity name
            /// {1}: Table name
            /// </summary>
            public const string FunctionTemplate_GetCount = @"
        public override int Get{0}Count()
        @
            SelectQuery query = new SelectQuery();
            query.Columns.Add(new SelectColumn(""*""));
            query.FromClause.BaseTable = FromTerm.Table(TableSchema.{1}.NameOfTable);

            DbCommand command = DBAccessor.GetDynamicCommandRowCount(query);

            try
            @
                return (int)DBAccessor.ExecuteScalar(command);
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return 0;
        $";


            /// <summary>
            /// {0}: Entity name
            /// {1}: Primary key
            /// {2}: Primary key data type
            /// {3}: Primary key local variable
            /// {4}: Table name
            /// {5}: Reader or Table command: CommandTemplate_Reader or CommandTemplate_Table
            /// </summary>
            public const string FunctionTemplate_GetByPrimaryKey = @"
        public override {0} Get{0}By{1}({2} {3})
        @
            SelectQuery query = new SelectQuery();
            query.Columns.Add(new SelectColumn(""*""));
            query.FromClause.BaseTable = FromTerm.Table(TableSchema.{4}.NameOfTable);
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
                            SqlExpression.Field(TableSchema.{4}.{1}),
                            SqlExpression.Parameter(TableSchema.{4}.{1}),
                            CompareOperator.Equal));

            DbCommand command = DBAccessor.GetDynamicCommand(query);
            DBAccessor.AddInParameter(command, TableSchema.{4}.{1}, {3});

            try
            @
                {5}
                {0}Mapper mapper = new {0}Mapper();
                return mapper.MapOne(result);
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return null;
        $";

            public const string FunctionTemplate_GetListByPrimaryKey = @"";

            public const string FunctionTemplate_GetListByPrimaryKeyWithPaging = @"";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key data type
            /// {3}: Foreign key local variable
            /// {4}: Table name
            /// {5}: Reader or Table command: CommandTemplate_Reader or CommandTemplate_Table
            /// </summary>
            public const string FunctionTemplate_GetByForeignKey = @"
        public override List<{0}> Get{0}By{1}({2} {3})
        @
            SelectQuery query = new SelectQuery();
            query.Columns.Add(new SelectColumn(""*""));
            query.FromClause.BaseTable = FromTerm.Table(TableSchema.{4}.NameOfTable);
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
                            SqlExpression.Field(TableSchema.{4}.{1}),
                            SqlExpression.Parameter(TableSchema.{4}.{1}),
                            CompareOperator.Equal));

            DbCommand command = DBAccessor.GetDynamicCommand(query);
            DBAccessor.AddInParameter(command, TableSchema.{4}.{1}, {3});

            try
            @
                {5}
                {0}Mapper mapper = new {0}Mapper();
                return mapper.Map(result);
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<{0}>();
        $";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key entity
            /// {3}: Foreign key local variable
            /// </summary>
            public const string FunctionTemplate_GetByForeignKeyEntity = @"
        public override List<{0}> Get{0}By{2}({2} {3})
        @
            if ({3} == null)
            @
                return new List<{0}>();
            $

            return Get{0}By{1}({3}.{1});
        $";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key data type
            /// {3}: Foreign key local variable
            /// {4}: Table name
            /// {5}: Primary key
            /// {6}: Reader or Table command: CommandTemplate_Reader or CommandTemplate_Table
            /// </summary>
            public const string FunctionTemplate_GetByForeignKeyWithPaging = @"
        public override List<{0}> Get{0}By{1}WithPaging({2} {3}, int pageIndex, int pageSize)
        @
            SelectQuery query = new SelectQuery();
            query.Columns.Add(new SelectColumn(""*""));
            query.FromClause.BaseTable = FromTerm.Table(TableSchema.{4}.NameOfTable);
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
                            SqlExpression.Field(TableSchema.{4}.{1}),
                            SqlExpression.Parameter(TableSchema.{4}.{1}),
                            CompareOperator.Equal));
            query.OrderByTerms.Add(new OrderByTerm(TableSchema.{4}.{5}, OrderByDirection.Ascending));

            DbCommand command = DBAccessor.GetDynamicCommand(query, pageIndex, pageSize);
            DBAccessor.AddInParameter(command, TableSchema.{4}.{1}, {3});

            try
            @
                {6}
                {0}Mapper mapper = new {0}Mapper();
                return mapper.Map(result);
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<{0}>();
        $";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key entity
            /// {2}: Foreign key
            /// {3}: Foreign key entity local variable            
            /// </summary>
            public const string FunctionTemplate_GetByForeignKeyEntityWithPaging = @"
        public override List<{0}> Get{0}By{1}WithPaging({1} {3}, int pageIndex, int pageSize)
        @
            if ({3} == null)
            @
                return new List<{0}>();
            $

            return Get{0}By{2}WithPaging({3}.{2}, pageIndex, pageSize);
        $";

            /// <summary>
            /// Get All
            /// {0}: Entity name
            /// TableSchema.{1}: Table name
            /// {2}: CommandTemplate_Table or CommandTemplate_Reader value
            /// </summary>
            public const string FunctionTemplate_GetAll = @"
        public override List<{0}> GetAll{0}()
        @
            SelectQuery query = new SelectQuery();
            query.Columns.Add(new SelectColumn(""*""));
            query.FromClause.BaseTable = FromTerm.Table(TableSchema.{1}.NameOfTable);

            DbCommand command = DBAccessor.GetDynamicCommand(query);

            try
            @
                {2}
                {0}Mapper mapper = new {0}Mapper();
                return mapper.Map(result);
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<{0}>();
        $";

            /// <summary>
            /// GetAllWithPaging
            /// {0}: Entity name
            /// TableSchema.{1}: Table name
            /// {2}: Primary key
            /// {3}: CommandTemplate_Table or CommandTemplate_Reader value            
            /// </summary>
            public const string FunctionTemplate_GetAllWithPaging = @"
        public override List<{0}> GetAll{0}WithPaging(int pageIndex, int pageSize)
        @
            SelectQuery query = new SelectQuery();
            query.Columns.Add(new SelectColumn(""*""));
            query.FromClause.BaseTable = FromTerm.Table(TableSchema.{1}.NameOfTable);
            query.OrderByTerms.Add(new OrderByTerm(TableSchema.{1}.{2}, OrderByDirection.Ascending));

            DbCommand command = DBAccessor.GetDynamicCommand(query, pageIndex, pageSize);

            try
            @
                {3}
                {0}Mapper mapper = new {0}Mapper();
                return mapper.Map(result);
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<{0}>();
        $";

            /// <summary>
            /// Add
            /// {0}: Entity name
            /// {1}: Entity local variable name
            /// TableSchema.{2}: Table name
            /// {3}: Parameter adding commands
            /// {4}: Parameter setting commands            
            /// </summary>
            public const string FunctionTemplate_Add = @"
        public override int Add{0}({0} {1})
        @
            InsertQuery query = new InsertQuery(TableSchema.{2}.NameOfTable);
{3}
            DbCommand command = DBAccessor.GetDynamicCommand(query);
{4}

            try
            @
                return DBAccessor.ExecuteNonQuery(command);
            $
            catch (System.Exception e)
            @
                bool bRethrow = ExceptionPolicy.HandleException(e, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw e;
                $
            $

            return 0;
        $";

            /// <summary>
            /// Update
            /// {0}: Entity name
            /// {1}: Entity local variable name
            /// {2}: Table name
            /// {3}: Parameter adding commands
            /// {4}: Parameter setting commands
            /// </summary>
            public const string FunctionTemplate_Update = @"
        public override int Update{0}({0} {1})
        @
            UpdateQuery query = new UpdateQuery(TableSchema.{2}.NameOfTable);
{3}
            DbCommand command = DBAccessor.GetDynamicCommand(query);
{4}         

            try
            @
                return DBAccessor.ExecuteNonQuery(command);
            $
            catch (System.Exception e)
            @
                bool bRethrow = ExceptionPolicy.HandleException(e, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw e;
                $
            $

            return 0;
        $";

            /// <summary>
            /// DeleteByPrimaryKey
            /// {0}: Entity name
            /// {1}: Primary key
            /// {2}: Primary key data type
            /// {3}: Primary key local variable name
            /// TableSchema.{4}: Table name
            /// </summary>
            public const string FunctionTemplate_DeleteByPrimaryKey = @"
        public override int Delete{0}By{1}({2} {3})
        @
            DeleteQuery query = new DeleteQuery(TableSchema.{4}.NameOfTable);

            query.WhereClause.Terms.Add(WhereTerm.CreateCompare(
                            SqlExpression.Field(TableSchema.{4}.{1}),
                            SqlExpression.Parameter(TableSchema.{4}.{1}),
                            CompareOperator.Equal));

            DbCommand command = DBAccessor.GetDynamicCommand(query);

            DBAccessor.AddInParameter(command, TableSchema.{4}.{1}, {3});

            try
            @
                return DBAccessor.ExecuteNonQuery(command);
            $
            catch (System.Exception e)
            @
                bool bRethrow = ExceptionPolicy.HandleException(e, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw e;
                $
            $

            return 0;
        $";

            /// <summary>
            /// DeleteByPrimaryEntity
            /// {0}: Entity name
            /// {1}: Primary key            
            /// {2}: Entity local variable name            
            /// </summary>
            public const string FunctionTemplate_DeleteByEntity = @"
        public override int Delete{0}({0} {2})
        @
            if ({2} == null)
            @
                return 0;
            $

            return Delete{0}By{1}({2}.{1});
        $";

            /// <summary>
            /// DeleteByForeignKey
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key data type
            /// {3}: Foreign key local variable name
            /// TableSchema.{4}: Table name
            /// </summary>
            public const string FunctionTemplate_DeleteByForeignKey = @"
        public override int Delete{0}By{1}({2} {3})
        @
            DeleteQuery query = new DeleteQuery(TableSchema.{4}.NameOfTable);

            query.WhereClause.Terms.Add(WhereTerm.CreateCompare(
                            SqlExpression.Field(TableSchema.{4}.{1}),
                            SqlExpression.Parameter(TableSchema.{4}.{1}),
                            CompareOperator.Equal));

            DbCommand command = DBAccessor.GetDynamicCommand(query);

            DBAccessor.AddInParameter(command, TableSchema.{4}.{1}, {3});

            try
            @
                return DBAccessor.ExecuteNonQuery(command);
            $
            catch (System.Exception e)
            @
                bool bRethrow = ExceptionPolicy.HandleException(e, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw e;
                $
            $

            return 0;
        $";

            /// <summary>
            /// DeleteByForeignEntity
            /// {0}: Entity name            
            /// {1}: Foreign key entity
            /// {2}: Foreign key 
            /// {3}: Foreign key entity local variable name
            /// </summary>
            public const string FunctionTemplate_DeleteByForeignEntity = @"
        public override int Delete{0}By{1}({1} {3})
        @
            if ({3} == null)
            @
                return 0;
            $

            return Delete{0}By{2}({3}.{2});
        $";

            /// <summary>
            /// FunctionTemplate_StoredProc
            /// {0}: Function name
            /// {1}: Return type
            /// {2}: Parameter list
            /// {3}: Add parameters to command
            /// </summary>
            public const string FunctionTemplate_StoredProc = @"
        public override {1} {0}({2})
        @
            DbCommand command = DBAccessor.GetStoredProcCommand(StoredProcSchema.{0}.NameOfStore);            

            try
            @
                {3}
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $
            
            return {4};
        $";

            /// <summary>
            /// FunctionTemplate_StoredProcReturnInt
            /// {0}: Function name
            /// {1}: Return type
            /// {2}: Parameter list declaration
            /// {3}: Parameter list
            /// {4}: Get parameter values
            /// </summary>
            public const string FunctionTemplate_StoredProcReturnInt = @"
        public override {1} {0}({2})
        @
            DbCommand command = DBAccessor.GetStoredProcCommand(StoredProcSchema.{0}.NameOfStore{3});

            try
            @
                int result = (int)DBAccessor.ExecuteNonQuery(command);
                {4}
                return result;
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $
            
            return 0;
        $";

            /// <summary>
            /// FunctionTemplate_StoredProc
            /// {0}: Function name
            /// {1}: Return type
            /// {2}: Parameters declaration
            /// {3}: Parameters list
            /// {4}: Execute command
            /// {5}: Get parameter values
            /// </summary>
            public const string FunctionTemplate_StoredProcReturnType = @"
        public override List<{1}> {0}({2})
        @
            DbCommand command = DBAccessor.GetStoredProcCommand(StoredProcSchema.{0}.NameOfStore{3});
            List<{1}> result = new List<{1}>();

            try
            @
                {4}
                {5}
                {1}Mapper mapper = new {1}Mapper();
                result = mapper.Map(return_value);
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $
            
            return result;
        $";

        }

        #endregion

        #region [ Linq data context project ]
        #endregion

        #region [ Linq mapper project ]

        public class LinqMapperProject
        {
            /// <summary>
            /// Mapper file name
            /// </summary>
            public const string CustomMapperFileName = "[EntityName]Mapper.cs.tpl";

            /// <summary>
            /// Mapper file name
            /// </summary>
            public const string GeneratedMapperFileName = "[EntityName]Mapper.generated.cs.tpl";

            /// <summary>
            /// Custom tag: EntityName
            /// </summary>
            public const string CustomTag_EntityName = "EntityName";

            /// <summary>
            /// Custom tag: EntityMapping
            /// </summary>
            public const string CustomTag_EntityMapping = "EntityMapping";

            public const string Mapping = @"
            target.{0} = source.{0};";
        }
        #endregion

        #region [ Linq project ]
        public class LinqProject
        {
            /// <summary>
            /// 
            /// </summary>
            public const string CustomProviderFileName = "[EntityName]Provider.cs.tpl";

            /// <summary>
            /// 
            /// </summary>
            public const string GeneratedProviderFileName = "[EntityName]Provider.generated.cs.tpl";

            /// <summary>
            /// 
            /// </summary>
            public const string GeneratedStoredProcProviderFileName = "[]StoredProcProvider.generated.cs.tpl";

            /// <summary>
            /// 
            /// </summary>
            public const string CustomStoredProcProviderFileName = "[]StoredProcProvider.cs.tpl";

            /// <summary>
            /// Custom tag EntityMethods
            /// </summary>
            public const string CustomTag_EntityMethods = "EntityMethods";

            /// <summary>
            /// Custom tag StoredProcedureMethods
            /// </summary>
            public const string CustomTag_StoredProcedureMethods = "StoredProcedureMethods";

            /// <summary>
            /// CommandTemplate_PrimaryKeyComparing
            /// {0}: Primary key
            /// {1}: object name
            /// </summary>
            public const string CommandTemplate_PrimaryKeyComparing = "o.{0} == {1}.{0}";

            /// <summary>
            /// FunctionTemplate_GetCount
            /// {0}: Entity name
            /// {1}: LInq table name
            /// </summary>
            public const string FunctionTemplate_GetCount = @"
        public override int Get{0}Count()
        @
            try
            @
                return DataContext.{1}.Count();
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return 0;
        $";


            /// <summary>
            /// {0}: Entity name
            /// {1}: Primary key
            /// {2}: Primary key data type
            /// {3}: Primary key local variable
            /// {4}: Entity local variable
            /// {5}: Linq entity table
            /// </summary>
            public const string FunctionTemplate_GetByPrimaryKey = @"
        public override Entity.{0} Get{0}By{1}({2} {3})
        @
            try
            @
                LinqContext.{0} {4} = 
                    DataContext.{5}.Where(o => o.{1} == {3}).FirstOrDefault();

                {0}Mapper mapper = new {0}Mapper();
                return mapper.MapOne({4});
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return null;
        $";

            public const string FunctionTemplate_GetListByPrimaryKey = @"";

            public const string FunctionTemplate_GetListByPrimaryKeyWithPaging = @"";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key data type
            /// {3}: Foreign key local variable
            /// {4}: Linq entity table
            /// {5}: Entity name local variable
            /// </summary>
            public const string FunctionTemplate_GetByForeignKey = @"
        public override List<Entity.{0}> Get{0}By{1}({2} {3})
        @
            try
            @
                IEnumerable<LinqContext.{0}> {5} =
                    DataContext.{4}.Where(o => o.{1} == {3});

                {0}Mapper mapper = new {0}Mapper();

                return mapper.Map({5});
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<Entity.{0}>();
        $";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key entity
            /// {3}: Foreign key local variable
            /// </summary>
            public const string FunctionTemplate_GetByForeignKeyEntity = @"
        public override List<Entity.{0}> Get{0}By{2}(Entity.{2} {3})
        @
            if ({3} == null)
            @
                return new List<Entity.{0}>();
            $

            return Get{0}By{1}({3}.{1});
        $";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key data type
            /// {3}: Foreign key local variable
            /// {4}: Linq member name
            /// {5}: Linq member local variable
            /// </summary>
            public const string FunctionTemplate_GetByForeignKeyWithPaging = @"
        public override List<Entity.{0}> Get{0}By{1}WithPaging({2} {3}, int pageIndex, int pageSize)
        @
            try
            @
                IEnumerable<LinqContext.{0}> {5} =
                    DataContext.{4}.Where(o => o.{1} == {3})
                                            .Skip(pageIndex * pageSize)
                                            .Take(pageSize).AsEnumerable();

                {0}Mapper mapper = new {0}Mapper();

                return mapper.Map({5});
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<Entity.{0}>();
        $";

            /// <summary>
            /// {0}: Entity name
            /// {1}: Foreign key entity
            /// {2}: Foreign key
            /// {3}: Foreign key entity local variable
            /// </summary>
            public const string FunctionTemplate_GetByForeignKeyEntityWithPaging = @"
        public override List<Entity.{0}> Get{0}By{1}WithPaging(Entity.{1} {3}, int pageIndex, int pageSize)
        @
            if ({3} == null)
            @
                return new List<Entity.{0}>();
            $

            return Get{0}By{2}WithPaging({3}.{2}, pageIndex, pageSize);
        $";

            /// <summary>
            /// Get All
            /// {0}: Entity name
            /// {1}: Linq member name
            /// {2}: Linq member local variable
            /// </summary>
            public const string FunctionTemplate_GetAll = @"
        public override List<Entity.{0}> GetAll{0}()
        @
            try
            @
                IEnumerable<LinqContext.{0}> {2} =
                    DataContext.{1}.Select(o => o);

                {0}Mapper mapper = new {0}Mapper();
                return mapper.Map({2});
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<Entity.{0}>();
        $";

            /// <summary>
            /// GetAllWithPaging
            /// {0}: Entity name
            /// {1}: Linq member name
            /// {2}: Linq member local variable
            /// </summary>
            public const string FunctionTemplate_GetAllWithPaging = @"
        public override List<Entity.{0}> GetAll{0}WithPaging(int pageIndex, int pageSize)
        @
            try
            @
                IEnumerable<LinqContext.{0}> {2} =
                    DataContext.{1}.Skip(pageIndex * pageSize)
                                    .Take(pageSize).AsEnumerable();

                {0}Mapper mapper = new {0}Mapper();
                return mapper.Map({2});
            $
            catch (Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return new List<Entity.{0}>();
        $";

            /// <summary>
            /// Add
            /// {0}: Entity name
            /// {1}: Entity local variable name
            /// {2}: Linq member name            
            /// </summary>
            public const string FunctionTemplate_Add = @"
        public override int Add{0}(Entity.{0} {1})
        @
            int result = 0;
            try
            @
                {0}Mapper mapper = new {0}Mapper();
                DataContext.{2}.InsertOnSubmit(mapper.MapOne({1}));
                DataContext.SubmitChanges();
                result = 1;
            $
            catch (System.Exception ex)
            @   
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return result;
        $";

            /// <summary>
            /// Update
            /// {0}: Entity name
            /// {1}: Entity local variable name
            /// {2}: Linq member name
            /// {3}: Primary key comparing
            /// </summary>
            public const string FunctionTemplate_Update = @"
        public override int Update{0}(Entity.{0} {1})
        @
            int result = 0;

            try
            @
                LinqContext.{0} linq{0} =
                                    DataContext.{2}
                                    .Where(o => {3})
                                    .FirstOrDefault();

                if (linq{0} != null)
                @
                    DataContext.{2}.InsertOnSubmit(linq{0});
                    DataContext.SubmitChanges();
                    result = 1;
                $
            $
            catch (System.Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return result;
        $";

            /// <summary>
            /// DeleteByPrimaryKey
            /// {0}: Entity name
            /// {1}: Primary key
            /// {2}: Primary key data type
            /// {3}: Primary key local variable name
            /// {4}: Linq member name
            /// </summary>
            public const string FunctionTemplate_DeleteByPrimaryKey = @"
        public override int Delete{0}By{1}({2} {3})
        @
            int result = 0;

            try
            @
                LinqContext.{0} linq{0} = 
                                    DataContext.{4}
                                    .Where(o => o.{1} == {3})
                                    .FirstOrDefault();

                DataContext.{4}.DeleteOnSubmit(linq{0});
                DataContext.SubmitChanges();
                result = 1;
            $
            catch (System.Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return result;
        $";

            /// <summary>
            /// DeleteByPrimaryEntity
            /// {0}: Entity name
            /// {1}: Primary key
            /// {2}: Entity local variable name
            /// </summary>
            public const string FunctionTemplate_DeleteByEntity = @"
        public override int Delete{0}(Entity.{0} {2})
        @
            if ({2} == null)
            @
                return 0;
            $

            return Delete{0}By{1}({2}.{1});
        $";

            /// <summary>
            /// DeleteByForeignKey
            /// {0}: Entity name
            /// {1}: Foreign key
            /// {2}: Foreign key data type
            /// {3}: Foreign key local variable name
            /// {4}: Linq member name
            /// </summary>
            public const string FunctionTemplate_DeleteByForeignKey = @"
        public override int Delete{0}By{1}({2} {3})
        @
            int result = 0;

            try
            @
                LinqContext.{0} linq{0} = 
                                    DataContext.{4}
                                    .Where(o => o.{1} == {3})
                                    .FirstOrDefault();

                DataContext.{4}.DeleteOnSubmit(linq{0});
                DataContext.SubmitChanges();
                result = 1;
            $
            catch (System.Exception ex)
            @
                bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);

                if (bRethrow)
                @
                    throw ex;
                $
            $

            return result;
        $";

            /// <summary>
            /// DeleteByForeignEntity
            /// {0}: Entity name
            /// {1}: Foreign key entity
            /// {2}: Foreign key
            /// {3}: Foreign key entity local variable name
            /// </summary>
            public const string FunctionTemplate_DeleteByForeignEntity = @"
        public override int Delete{0}By{1}(Entity.{1} {3})
        @
            if ({3} == null)
            @
                return 0;
            $

            return Delete{0}By{2}({3}.{2});
        $";

            /// <summary>
            /// FunctionTemplate_StoredProc
            /// {0}: Function name
            /// {1}: Return type
            /// {2}: Parameter list
            /// {3}: Add parameters to command
            /// </summary>
            public const string FunctionTemplate_StoredProc = @"
                public override {1} {0}({2})
                @
                    DbCommand command = DBAccessor.GetStoredProcCommand(StoredProcSchema.{0}.NameOfStore);            
        
                    try
                    @
                        {3}
                    $
                    catch (Exception ex)
                    @
                        bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);
        
                        if (bRethrow)
                        @
                            throw ex;
                        $
                    $
                    
                    return {4};
                $";

            /// <summary>
            /// FunctionTemplate_StoredProcReturnInt
            /// {0}: Function name
            /// {1}: Return type
            /// {2}: Parameter list declaration
            /// {3}: Parameter list
            /// {4}: Get parameter values
            /// </summary>
            public const string FunctionTemplate_StoredProcReturnInt = @"
                public override {1} {0}({2})
                @
                    DbCommand command = DBAccessor.GetStoredProcCommand(StoredProcSchema.{0}.NameOfStore{3});
        
                    try
                    @
                        int result = (int)DBAccessor.ExecuteNonQuery(command);
                        {4}
                        return result;
                    $
                    catch (Exception ex)
                    @
                        bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);
        
                        if (bRethrow)
                        @
                            throw ex;
                        $
                    $
                    
                    return 0;
                $";

            /// <summary>
            /// FunctionTemplate_StoredProc
            /// {0}: Function name
            /// {1}: Return type
            /// {2}: Parameters declaration
            /// {3}: Parameters list
            /// {4}: Execute command
            /// {5}: Get parameter values
            /// </summary>
            public const string FunctionTemplate_StoredProcReturnType = @"
                public override List<{1}> {0}({2})
                @
                    DbCommand command = DBAccessor.GetStoredProcCommand(StoredProcSchema.{0}.NameOfStore{3});
                    List<{1}> result = new List<{1}>();
        
                    try
                    @
                        {4}
                        {5}
                        {1}Mapper mapper = new {1}Mapper();
                        result = mapper.Map(return_value);
                    $
                    catch (Exception ex)
                    @
                        bool bRethrow = ExceptionPolicy.HandleException(ex, Rose.Data.Constant.EH_DataAccessPolicy);
        
                        if (bRethrow)
                        @
                            throw ex;
                        $
                    $
                    
                    return result;
                $";
        }

        #endregion
    }
}
